USE [Monitoring]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMarkers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



CREATE PROCEDURE [dbo].[GetMarkers]
(
	@gameName nvarchar(50),
	@login nvarchar(50),
	@password nvarchar(50)
)
AS
BEGIN

	-- check login and password
	DECLARE @teamID int
	SELECT 
		@teamID = TeamID
	FROM
		dbo.Person
	WHERE
		[Login] = @login AND Password = @password
	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR (N''Login and Password are incorrect'', 13, 1)
		RETURN -1
	END

	-- check last game
	IF NOT EXISTS 
		(
		SELECT * FROM dbo.Game
		WHERE [Name]=@gameName AND TeamID=@teamID)
	BEGIN
		RAISERROR (N''Person ''''%s''''does not belong to game ''''%s'''''', 13, 1, @login, @gameName)
		RETURN -1
	END

	-- get all information about makers for game
	SELECT
		P.MarkerName, 
		P.GameName, 
		P.Latitude, 
		P.Longitude, 
		P.Altitude, 
		P.Speed
	FROM dbo.GPSPoint P
	JOIN (
			-- get last point for marker and game
			SELECT MarkerName, GameName, MAX([Time]) [Time] 
				FROM GPSPoint 
				WHERE GPSPoint.GameName = @gameName 
				GROUP BY MarkerName, GameName
		) LP ON P.MarkerName = LP.MarkerName 
			AND P.GameName = LP.GameName
			AND P.[Time] = LP.[Time]
	WHERE 
		P.GameName = @gameName
END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPoint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



CREATE PROCEDURE [dbo].[AddPoint]
(
	@login nvarchar(50),
	@password nvarchar(50),
	@markerName nvarchar(50),
	@gameName nvarchar(50),
	@latitude float,
	@longitude  float,
	@altitude float = NULL,
	@speed int = NULL
)
AS
BEGIN
	BEGIN TRAN
	-- Insert new value in [GPSPoint]
	INSERT INTO [dbo].[GPSPoint]
           ([MarkerName]
           ,[GameName]
           ,[Login]
           ,[Latitude]
           ,[Longitude]
           ,[Speed]
           ,[Altitude]
			,[PersonID])
	-- check for existing person
	SELECT 
			@markerName,
			@gameName,
			@login,
			@latitude,
			@longitude,
			@speed,
			@altitude,
			Person.ID
	FROM [dbo].[Person]
	WHERE
		[Login]=@login AND [Password]=@password

	-- check result
	IF @@ROWCOUNT = 0
	BEGIN
		ROLLBACK
		RAISERROR (N''Login and Password are incorrect'', 13, 1)
		RETURN -1
	END

	-- get last ID
	SELECT SCOPE_IDENTITY()
	COMMIT

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPointHashed]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[AddPointHashed]
(
	@hash datetime,
	@latitude float,
	@longitude  float,
	@altitude float = NULL,
	@speed int = NULL
)
AS
BEGIN
	BEGIN TRAN

	DECLARE @currentdate datetime
	SELECT @currentdate=getdate()

	-- Insert new value in [GPSPoint]
	INSERT INTO [dbo].[GPSPoint]
           ([MarkerName]
           ,[GameName]
           ,[Login]
           ,[Latitude]
           ,[Longitude]
           ,[Speed]
           ,[Altitude]
			,[PersonID])
	-- check for existing hash
	SELECT
		[MarkerName],
		[GameName],
		[MarkerHash].[Login],
		@latitude, 
		@longitude, 
		@speed, 
		@altitude,
		[Person].ID
	FROM
		[dbo].[MarkerHash]
	INNER JOIN
		[dbo].[Person] ON [MarkerHash].[Login] = [Person].[Login]
	WHERE
		[Hash] = @hash AND DATEDIFF(day, [Hash], @currentdate)<1

	-- check result
	IF @@ROWCOUNT = 0
	BEGIN
		ROLLBACK
		-- check for valid hash
		IF NOT EXISTS (SELECT * FROM [dbo].[MarkerHash] WHERE [Hash] = @hash)
			RAISERROR (N''Hash is incorrect'', 13, 1)
		ELSE
			-- check for expiration
			IF (SELECT ABS(DATEDIFF(day, [Hash], @currentdate))
			FROM
				[dbo].[MarkerHash]
			WHERE
				[Hash] = @hash) >= 1
			RAISERROR (N''Hash is expired. Relogin is needed.'', 13, 2)
		
		RAISERROR (N''Unknown error'', 13, 3)
		RETURN -1
	END

	SELECT SCOPE_IDENTITY()
	COMMIT

END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MarkerLogin]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[MarkerLogin]
(
	@markerName nvarchar(50),
	@gameName nvarchar(50),
	@login nvarchar(50),
	@password nvarchar(50)
)
AS
BEGIN
	BEGIN TRAN
	DECLARE @hash datetime
	SELECT @hash = getdate()
	FROM
		[dbo].[Person]
	WHERE
		@login = [Person].[Login] AND @password = [Person].[Password]

	-- Check login and password
	IF @hash IS NULL
	BEGIN
		ROLLBACK
		RAISERROR (N''Login and Password are incorrect'', 13, 1)
		RETURN -1
	END

	-- Insert new value in [MarkerHash]
	INSERT INTO [dbo].[MarkerHash]
           (
			[Hash]
			,[MarkerName]
			,[GameName]
			,[Login]
			)
	-- check for existing hash
	SELECT
			@hash,
			@markerName,
			@gameName,
			@login
	FROM
		[dbo].[Person]
	WHERE
		@login = [Person].[Login] AND @password = [Person].[Password]

	SELECT @hash

	COMMIT

END



' 
END
GO
